#include <mysql.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "include/sql.h"
#include "include/divId.h"
#include "include/parser.h"
#include "include/acsLogger.h"

MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

char new_query[QUERY_SIZE];
char new_query1[QUERY_SIZE];
char sel_reboot_query[QUERY_SIZE];
char update_reboot_query[QUERY_SIZE];
char fw_finish_xml[1024];

char* select_model_id = "SELECT id FROM `model` where `name`= \"%s\";";
char* select_param_name = "SELECT p.name FROM parameter p,model m WHERE p.model_id= m.id and m.name=\"%s\" ";
char* sel_model_name_from_db = "SELECT `name` FROM model WHERE `name`= \"%s\";";
char* sel_dev_id = "SELECT `serial_number` FROM `device` WHERE serial_number = \"%s\"";
char* set_value_query = "SELECT `serial_number`,`name` ,`param_type`,h.`param_value` FROM history h left join `device_param` on h.`device_param_id` = device_param.`id` left join  parameter on parameter.id = device_param.param_id WHERE applied = 0 && serial_number =\"%s\" LIMIT 8;";
char* set_value_answer_query = "UPDATE history set applied = %s WHERE applied = 0 and device_param_id  in (Select d.id from device_param  d WHERE d.serial_number = \"%s\")";
char* set_param_value_query = "SELECT p.name FROM parameter p,model m WHERE p.model_id= m.id and m.name=\"%s\";";
char* get_param_value_ids = "SELECT p.id FROM parameter p,model m WHERE p.model_id= m.id and m.name=\"%s\";";
char* insert_param_value_device = "INSERT INTO `device_param`(`serial_number`,`param_id`,`param_value`) VALUES(\"%s\",%s,\"%s\");";
char* check_param_exists = "SELECT `serial_number`,`param_id` FROM `device_param` WHERE serial_number=\"%s\" AND param_id=%s ";
char* update_param_value_device = "UPDATE `device_param` SET `param_value` = \"%s\" WHERE `param_id`= %s AND `serial_number`=\"%s\";";
char* set_param_value_history = "INSERT INTO `history`(`param_value`,`applied`,`device_param_id`) VALUES(\"%s\",4,%s);";
char* get_device_param_value_ids= "SELECT d.id FROM device_param d WHERE d.serial_number=\"%s\";";
char* insert_dev_id = "INSERT INTO device(serial_number,manufacturer,oui,product_class,subscriber_id,network_id,model_id) \
		      VALUES(\"%s\",\"%s\",\"%s\",\"%s\",1,1,\"%s\")";
char* sel_history_id = "SELECT h.id FROM history h,device_param d WHERE h.device_param_id = d.id and d.serial_number = \"%s\" LIMIT %i";
char* insert_unknown = "INSERT INTO `unknown`(`serial`,`model_name`,`unknown_type`) VALUES(\"%s\",\"%s\",\"%s\");";
char* update_unknown = "UPDATE `unknown` SET `serial`=\"%s\",`model_name`=\"%s\",`unknown_type`=\"%s\";";
char* sel_unknown = "SELECT `serial`,`model_name` FROM `unknown` WHERE  `serial` = \"%s\";";
char* insert_reboot = "INSERT INTO `reboot`(`serial_number`,`reboot_status`) VALUES(\"%s\",1);";
char* sel_reboot = "SELECT `serial_number`,`reboot_status` FROM `reboot` WHERE `reboot_status` = 0 && `serial_number` = \"%s\";";
char* check_reboot_exists = "SELECT `serial_number` FROM `reboot` WHERE `serial_number`= \"%s\";";
char* update_reboot = "UPDATE `reboot` SET `reboot_status`= 1 WHERE serial_number = \"%s\";";
char* sel_fw =  "SELECT `file_type`,`path`,`file_size` FROM file WHERE  `id` = (SELECT `file_id` FROM `download` WHERE `serial_number`=\"%s\" AND `status` = 0);";
char* fw_ifexist = "SELECT `serial_number` from download WHERE `status`= 0 AND `serial_number` = \"%s\";";
char* done_upgrade = "UPDATE `download` SET `status`= %i WHERE `serial_number` = \"%s\";";
/**
  Function Name     : sqlCon 
  Input Paramaters  : NONE
  Output Paramaters : NONE 
  Result            : SUCCESS/FAILURE 
  Description       : creates connection with mysql server 
*/
int sqlCon()
{     
   conn = mysql_init(NULL);
   if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0)) 
   {
    //  printf("\n%s (sqlCon)\n", mysql_error(conn));
    logtr("Can not create MySQL connection",(char*)mysql_error(conn), ERROR, conferr);
      return FAILURE;
   }
   else
      return  SUCCESS;

}

/**
  Function Name     : init_dev_id 
  Input Paramaters  : pointers to manufacturer,oui,product Class,serial Number and deviceId struct
  Output Paramaters : NONE 
  Result            : NONE 
  Description       : initializes deviceId struct data
*/
void init_dev_id( char* model,char* serial_number,char* manufact,char* oui,char* product_class, deviceId* dev_id)
{
  if( model && serial_number && manufact && oui && product_class )
  {
    memset(dev_id->modelName,0,sizeof(dev_id->modelName));
    memset(dev_id->serialNumber,0,sizeof(dev_id->serialNumber));
    memset(dev_id->manufacturer,0,sizeof(dev_id->manufacturer));
    memset(dev_id->oui,0,sizeof(dev_id->oui));
    memset(dev_id->productClass,0,sizeof(dev_id->productClass));
    
    strcpy(dev_id->modelName,model);
    strcpy(dev_id->serialNumber,serial_number);
    strcpy(dev_id->manufacturer,manufact);
    strcpy(dev_id->oui,oui);
    strcpy(dev_id->productClass,product_class);
  }
//   else
//      printf("\nModel or Serial or Manufact or Oui or ProductClass is NULL(init_dev)");
 
}


/** 
  Function Name     : check_device_id 
  Input Paramaters  : pointer to deviceId struct
  Output Paramaters : NONE 
  Result            : SUCCESS/FAILURE 
  Description       : check cpe device id in db
*/
int check_device_id( char* serialNumber )
{
  int count = 0;
  if( serialNumber )
  {
      char sel_dev_id_query[256];
      memset(sel_dev_id_query,0,256);
      sprintf(sel_dev_id_query,sel_dev_id,serialNumber);
      if( sqlCon() < 0 ){
	logtr("Can not create MySQL connection",(char*)mysql_error(conn), ERROR, conferr);
	//printf("\nNot connection\n");
	noSqlError = 1;
      }
      else
      {
	  if( mysql_query( conn, sel_dev_id_query )) 
	  {
	     logtr("MySQL: ","Check Your SQL configuration", INFO, conferr);      
             logtr("MySQL: ","Can not execute command", INFO, conferr);
	      //printf("\nPlease chack sql server configuration(check_device_id)");
	      //fprintf(stderr,"\n%s\n", mysql_error(conn));
	      noSqlError = 1;
	  }
	  else
	  {
	      res = mysql_use_result(conn);
	      while (( row = mysql_fetch_row(res)) != NULL )
	      { 
		  count++;
	      }
		mysql_free_result(res);
	  }
	    mysql_close(conn);
      }
  }
//    else
//      printf("\n Serial is NULL (check_device_id)");
  
   return ( count > 0 ) ? SUCCESS : FAILURE;
}

/**
  Function Name     : check_model_name
  Input Paramaters  : model name
  Output Paramaters : NONE 
  Result            : SUCCESS/FAILURE 
  Description       : check cpe model name in db
*/ 
int check_model_name( char* model )
{
  int count = 0;
  if( model )
  {
	char check_model_name_query[256];
	memset(check_model_name_query,0,256);
	sprintf(check_model_name_query,sel_model_name_from_db,model);
	
	//connecting whit sqlConnect
	if( sqlCon() < 0 ){
	  logtr("error establishing a MySQL connection",(char*)mysql_error(conn), ERROR, conferr);
	  //printf("Not connection");
	  noSqlError = 1;
	}
	else
	{
	    if( mysql_query( conn,check_model_name_query ) ) 
	    {
	      logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
	      logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	      //printf("\nPlease chack sql server configuration(check_model_name)");
 	      //fprintf(stderr,"\n%s\n", mysql_error(conn));
	      noSqlError = 1;
	    }
	    else
	    {
		res = mysql_use_result(conn);
		logtr( model,"MySQL: Checking Model Name:", INFO, conferr);
		
		while (( row = mysql_fetch_row(res)) != NULL )
		{ 
		   //printf("\nThis is a Our CPE,We have a model name");
		   logtr( model,"MySQL: This is a Our CPE,We have a model name:", INFO, conferr);
		   count++;
		}
		  mysql_free_result(res);
	    }
	      mysql_close(conn);
	}
  }
//    else
//      printf("\n Model name is Null");
  
   return ( count > 0 ) ? SUCCESS : FAILURE;
    
}

/**
  Function Name     : set_device_db 
  Input Paramaters  : pointer to deviceId struct
  Output Paramaters : NONE 
  Result            : INSERT OR UPDATE DB
  Description       : set cpe device id data in db
*/
void set_device_db( char* serialNumber,char* manufacturer,char* oui,char* productClass,char* model )
{
  
 if( serialNumber && manufacturer && oui && productClass && model )
 {
  char insert_dev_id_query[256];
  char insert_reboot_query[256];
  
  char model_id[5];
  memset(model_id,0,5);
  memset(insert_dev_id_query,0,256);
  memset(insert_reboot_query,0,256);

   //connecting whit sqlConnect
  if( sqlCon() < 0 ){
    logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
    //printf("Not connection");
    noSqlError = 1; 
  }
  else
  {
      sprintf( insert_dev_id_query,insert_dev_id,serialNumber,manufacturer,oui,productClass,get_model_id(model));
      sprintf( insert_reboot_query,insert_reboot,serialNumber,1 );

      char dev_id_info[256];
      sprintf(dev_id_info,"OK set device paramaters: [serialNumber: %s, manufacturer: %s ,oui: %s,productClass: %s ] in DB ",
      serialNumber,manufacturer,oui,productClass);
      
      logtr(insert_dev_id_query,"insert_dev_id_query", INFO, conferr); 
    
      res = mysql_use_result(conn);
      if ( mysql_query( conn,insert_dev_id_query ) ) 
      {
	logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr); 
        logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	//printf("\nPlease chack sql server configuration(set_device_db1)");
 	//fprintf(stderr,"\n%s\n", mysql_error(conn));  
	 noSqlError = 1; 
      }
      else
	logtr("MySQL: ",dev_id_info, INFO, conferr);
	//printf("\nOK set device in DB");
      
      mysql_free_result(res);
      
       memset(dev_id_info,0,256);
       sprintf(dev_id_info,"OK set reboot in DB: [serialNumber: %s, manufacturer: %s ,oui: %s,productClass: %s ] in DB ",
       serialNumber,manufacturer,oui,productClass);
   
      if( check_reboot_tbl( serialNumber ) == 1 )
      {
	  if ( mysql_query( conn,insert_reboot_query ) ) 
	  {
	    logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr); 
            logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
	    //printf("\nPlease chack sql server configuration(set_device_db2)");
	    //fprintf(stderr,"\n%s\n", mysql_error(conn));   
	     noSqlError = 1; 
	  }
	else{
	  logtr("MySQL: ",dev_id_info, INFO, conferr);
	  //printf("\nOK set reboot in DB");
	}
      }
   
  }
  mysql_close(conn);
 }
//   else
//     printf("\n Serial Number or Manufacturer or Oui or Product Class (set_device_db)");
}

/**
  Function Name     : set_param_value_struct_xml 
  Input Paramaters  : int count,char pointers parameter value xml,parameter name,parameter type,parameter value
  Output Paramaters : char pointer 
  Result            : ret_one_par_val_str
  Description       : set cpe device id data in db
*/
char* set_param_value_struct_xml( int count,char* param_value_struct,char* par_name,char* par_type,char* par_value )
{
  
  if( count == 0 ){
    ret_one_par_val_str = "";
  }
  else
  {
    char par_val_str[count*512];
    memset(par_val_str,0,sizeof(par_val_str));
    sprintf(par_val_str,param_value_struct,par_name,par_type,par_value);
    
    ret_one_par_val_str = (char*)par_val_str;
  }
  
  return (char*)ret_one_par_val_str;
}

/**
  Function Name     : create_set_value_xml 
  Input Paramaters  : parameter value xml,setValue,serial number
  Output Paramaters : char pointer 
  Result            : myreturn
  Description       : create xml file set value 
*/
char* create_set_value_xml(char* param_value_struct,char* setValue,char* dev_id_serial )
{
    memset(return_set_value_xml,0,2048);
    if( param_value_struct && setValue && dev_id_serial )
    {
	int count = 0;
	if( sqlCon() < 0 )
	{
	   logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
	  //printf("Not connection");
	   noSqlError = 1; 
	}
	else
	{
	    sprintf(new_query,set_value_query,dev_id_serial);
	    
	    if ( mysql_query(conn,new_query) ) 
	    {
	      logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr);  
	      logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
	      //printf("\nPlease chack sql server configuration( create_set_value_xml )");
	      //fprintf(stderr,"\n%s\n", mysql_error(conn));
	      noSqlError = 1; 
	    }
	    else
	    {
	      char all_param_val_str_xml[2048];
	      char set_value_xml[2048];
	      memset(all_param_val_str_xml,0,sizeof(all_param_val_str_xml));
	      memset(set_value_xml,0,sizeof(set_value_xml));
	      
	      res = mysql_use_result(conn);
	      while((row = mysql_fetch_row(res)) != NULL)
	      { 
		if(strcmp( dev_id_serial,row[0]) == 0 )
		count++;
		strcat(all_param_val_str_xml,set_param_value_struct_xml( count, param_value_struct,row[1],row[2],row[3]));
	      }
		if(!count)
		{
		//printf("\nNOT ANY CHANGE IN THIS CPE");
		logtr(dev_id_serial,"MySQL: NOT ANY CHANGE IN THIS CPE with this serialNumber ->", INFO, conferr);
		  strcpy(return_set_value_xml,"NONE");
		}
		else{
		    sprintf(set_value_xml,setValue,count,all_param_val_str_xml);
		    strcpy(return_set_value_xml,set_value_xml);
	      }
		      mysql_free_result(res);
	    }

	      mysql_close(conn);
	}
    }
// 	else
// 	  printf("\n Param_value_struct or SetValue or Dev_id_serial ( create_set_value_xml )");
    
   return return_set_value_xml;
}

/**
  Function Name     : set_value_answer_insert_db 
  Input Paramaters  : char pointer  set_value_answer_num,serial number
  Output Paramaters : NONE
  Result            : INSERT OR UPDATE DB
  Description       : set answer in db
*/
void set_value_answer_insert_db( char* set_value_answer_num,char* serial_number )
{
  if( set_value_answer_num && serial_number )
  {
	memset( new_query,0,sizeof( new_query ) );
	
	if( sqlCon() < 0 ){
	  logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
	 //printf("Not connection");
	  noSqlError = 1; 
	}
	if( strcmp(set_value_answer_num,"0") == 0 )
	{
	  sprintf( new_query,set_value_answer_query,"1",serial_number );
	  
	  if ( mysql_query( conn,new_query ) ) 
	  {
	   logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr);  
	   logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	    //printf("\nPlease chack sql server configuration(set_value_answer_insert_db1)");
 	    //fprintf(stderr,"\n%s\n", mysql_error(conn));   
	     noSqlError = 1; 
	  }
	  else
	  {
	     char set_answ[128];
	     sprintf(set_answ,"OK set value in db with this serialnumber: %s,setValue answer = 0.", serial_number);
	     logtr("MySQL: ",set_answ, INFO, conferr); 
	     //printf("\nOK set value in db,setValue answer = 0");
	  }
	}
	
	else if( strcmp(set_value_answer_num,"1") == 0 )
	{
	  memset(new_query,0,sizeof(new_query));
	  sprintf(new_query,set_value_answer_query,"2",serial_number);
	  if ( mysql_query( conn,new_query ) ) 
	  {
	   logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr);  
	   logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	    //printf("\nPlease chack sql server configuration");
 	    //fprintf(stderr,"\n%s\n", mysql_error(conn));  
	     noSqlError = 1; 
	  }
	  else
	  {
	     char set_answ[128];
	     sprintf(set_answ,"CPE can't change any value with this serialnumber: %s,setValue answer = 1.", serial_number);
	     logtr("MySQL: ",set_answ, INFO, conferr); 
	     //printf("\nCPE can`t change any value,setValue ansver = 1");
	  }
	}
      else
      {
	  sprintf(new_query,set_value_answer_query,"3",serial_number);
	if ( mysql_query( conn,new_query ) ) 
	{
	   logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr);  
	   logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	  //printf("\nPlease chack sql server configuration(set_value_answer_insert_db2)");
 	  //fprintf(stderr,"\n%s\n", mysql_error(conn)); 
	   noSqlError = 1; 
	}
	  else
	  {
	     char set_answ[128];
	     sprintf(set_answ,"CPE can't change any value failur code for this serialnumber: %s,setValue answer = 1.", serial_number);
	     logtr("MySQL: ",set_answ, INFO, conferr); 
	    //printf("\nCPE can`t change any value failur code");
	  }
	}
	mysql_close(conn);
  }
//    else
//      printf("\n Set_value_answer_num or Serial_number (set_value_answer_insert_db)");
 }
 
/**
  Function Name     : get_param_name_value 
  Input Paramaters  : count,get_param_value_string,param_name
  Output Paramaters : NONE
  Result            : ret_one_par_val_str
  Description       : create get prameter name xml part db
*/
char* get_param_name_value( int count,char* get_param_value_string,char* param_name)
{
  ret_one_par_val_str = "";
  
  if( count != 0 && get_param_value_string && param_name )
  {
    char par_val_str[count*512];
    memset((char*)par_val_str,0,sizeof(par_val_str));
    sprintf( (char*)par_val_str,get_param_value_string,param_name);
    ret_one_par_val_str = (char*)par_val_str;
  }
//    else
//      printf("\n Count = 0 or get_param_value_string or param_name is NULL ( get_param_name_value )");
  
  return (char*)ret_one_par_val_str;
}

/**
  Function Name     : create_getParam_name_value_xml 
  Input Paramaters  : parameter value xml,setValue,serial number
  Output Paramaters : char pointer 
  Result            : myreturn
  Description       : create xml file set value for cpe
*/
char* create_getParam_name_value_xml( char* get_param_value_string,char* get_param_value,char* model_name )
{
  memset(get_value_xml,0,sizeof(get_value_xml));
	
  if( get_param_value_string && get_param_value && model_name )
  {
      int count = 0;
      if( sqlCon() < 0 )
      {
	logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
	//printf("Not connection");
	 noSqlError = 1; 
      }
      else
      {
	memset(new_query,0,sizeof(new_query));
	sprintf(new_query,set_param_value_query,model_name);
	if ( mysql_query(conn,new_query) ) 
	{
	    logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
	    logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
	    //printf("\nPlease chack sql server configuration(create_getParam_name_value_xml)");
	    //fprintf(stderr,"\n%s\n", mysql_error(conn));
	     noSqlError = 1; 
	  
	}
	else
	{
	  char* all_param_val_str_xml = (char*)malloc(4096);
	  memset(all_param_val_str_xml,0,4096);

	  res = mysql_use_result(conn);
	  while((row = mysql_fetch_row(res)) != NULL)
	  { 
	    count++;
	    strcat(all_param_val_str_xml,get_param_name_value( count,get_param_value_string,row[0] ));
	  }
	  sprintf( get_value_xml,(char*)get_param_value,count + 5,all_param_val_str_xml );
	  free(all_param_val_str_xml);
	}
	
	mysql_free_result(res);
	mysql_close(conn);
    }
  }
//     else
//       printf("\n Get_param_value_string or Get_param_value or Model_name is NULL (create_getParam_name_value_xml)");
  
   return get_value_xml;
}

/**
  Function Name     : set_get_value_db 
  Input Paramaters  : object getParamValues struct && model name,serial number
  Output Paramaters : NONE
  Result            : INSERT OR UPDATE DB
  Description       : set data
*/
void set_get_value_db( getParamNames getParNam,getParamValues getParVal,getNewParamValues getNewParVal,char* model_name,char* serial_number )//????????????????????????????????????????
{
  if( model_name && serial_number )
  {
      char set_dev_param_values_query[256];
      char check_param_exists_query[256];
      int id_count = 0,enter_device = 0,enter_history = 0;
      
      //connecting whit sql
      if( sqlCon() < 0 )
      {
	logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
	//printf("Not connection");
	 noSqlError = 1; 
      }
      else
      {
	parameterFilter( getParNam, getParVal, getNewParVal, model_name);
	int count = 0,row_num = 0,i;
	id_count = get_param_name_db_id( model_name );
	
	for(i = 0; i < id_count ;i++)
	{
	  sprintf(check_param_exists_query,check_param_exists,serial_number,out_par_name_id[i].out_id);
	  if( mysql_query( conn,check_param_exists_query ))
	  {
	      logtr(serial_number,"MYSQL: can't realease check_param_exists_query query with this serialNumber: ", ERROR, conferr);
	      logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	      //printf("\nPlease chack sql server configuration");
 	      //fprintf(stderr,"\n%s\n", mysql_error(conn));
	       noSqlError = 1; 
	  }
	  else
	  {
	    res = mysql_use_result(conn);
	    while((row = mysql_fetch_row(res)) != NULL)
		row_num++;
	      
	    mysql_free_result(res);
	  }
	}
	
	for( i = 0; i < id_count ;i++ )
	{
	  if( row_num != id_count  ){
	    memset(set_dev_param_values_query,0,sizeof( set_dev_param_values_query ));
	    sprintf(set_dev_param_values_query,insert_param_value_device,serial_number,out_par_name_id[i].out_id,getNewParVal[i].getNewValue);
	  }
	  else{
	    memset(set_dev_param_values_query,0,sizeof( set_dev_param_values_query ));
	    sprintf(set_dev_param_values_query,update_param_value_device,getNewParVal[i].getNewValue,out_par_name_id[i].out_id,serial_number);
	  }
	  /* send SQL query */
	  if ( mysql_query( conn,set_dev_param_values_query ) ) 
	  {
	     logtr(serial_number,"MYSQL: can't set or update values in DB device parameters with this serialNumber: ", ERROR, conferr);
             logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
	     //printf("\nPlease chack sql server configuration( set_get_value_db1 )");
	     //fprintf(stderr,"\n%s\n", mysql_error(conn));
	       noSqlError = 1; 
	  }
	  else
	  {
	    count++;
	    if( enter_device == 0 )
	    {
	     //printf("\nOK set or update values in DB device parameters");
	      logtr(serial_number,"MYSQL: OK set or update values in DB device parameters with this serialNumber: ", INFO, conferr);
	      enter_device = 1;
	    }
	    
	  }
	  memset(set_dev_param_values_query,0,sizeof(set_dev_param_values_query));
	}
	  if( count == id_count )
	  {
	      if( check_history( serial_number ,id_count) == FAILURE )
	      {
	      get_device_param_name_db_id( serial_number );
	      for( i=0;i < id_count;i++ )
	      {
		sprintf(set_dev_param_values_query,set_param_value_history,getNewParVal[i].getNewValue,out_par_name_id[i].out_id_1);
		if ( mysql_query( conn,set_dev_param_values_query ) ) 
		{
		  logtr(serial_number,"MYSQL: can't set or update Values in DB history with this serialNumber: ", ERROR, conferr);
	          logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
		  //printf("\nPlease chack sql server configuration( set_get_value_db2 )");
		  //fprintf(stderr,"\n%s\n", mysql_error(conn));
		   noSqlError = 1; 
		}
		else{
		   if( enter_history == 0 )
	           {
		      //printf("\nOK set or update Values in DB history");
		      logtr(serial_number,"MYSQL: OK set or update Values in DB history with this serialNumber: ", INFO, conferr);
		     enter_history = 1;
		   }
		}
		
		  memset( set_dev_param_values_query,0,sizeof(set_dev_param_values_query) );
	      }
	      count = 0;
	    } 
	}
      }
      mysql_close(conn);
  }
//    else
//      printf("\n Model_name && Serial_number is NULL ( set_get_value_db )");
}

/**
  Function Name     : get_param_name_db_id 
  Input Paramaters  : model name
  Output Paramaters : int count
  Result            : count
  Description       : return cpe parameter count from db
 */

int get_param_name_db_id( char* model_name )
{
  int count = 0;
  
  if( model_name )
  {
  char get_param_name_id_query[128];
  memset(get_param_name_id_query,0,sizeof(get_param_name_id_query));  
  
    sprintf( get_param_name_id_query,get_param_value_ids,model_name );

    if ( mysql_query( conn,get_param_name_id_query ) ) 
    {
       logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr); 
       logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
       //printf("\nPlease chack sql server configuration(get_param_name_db_id1)");
       //fprintf(stderr,"\n%s\n", mysql_error(conn));
	 noSqlError = 1; 
    }
    else
    {
      res = mysql_use_result(conn);
      while((row = mysql_fetch_row(res)) != NULL)
     {
       strcpy(out_par_name_id[count].out_id,row[0]);
       count++;
     }
     mysql_free_result(res);
    }
  }
//    else
//      printf("\n Model_name is NUll (get_param_name_db_id)");
  
   return count;
}
/**
  Function Name     : get_device_param_name_db_id 
  Input Paramaters  : serial number
  Output Paramaters : NONE
  Result            : out_par_name_id struct
  Description       : insert divace parameter id in aut_par_name_id struct
 */

void get_device_param_name_db_id( char* serial_number )
{
  if( serial_number )
  {
      char get_dev_param_id_query[128];
      memset(get_dev_param_id_query,0,sizeof(get_dev_param_id_query));
      
	sprintf(get_dev_param_id_query,get_device_param_value_ids,serial_number);
	if( mysql_query( conn,get_dev_param_id_query ) )
	{
	    logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr); 
            logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
	  //printf("\nPlease chack sql server configuration(get_device_param_name_db_id)");
	  //fprintf(stderr,"\n%s\n", mysql_error(conn));
	   noSqlError = 1; 
	}
	else
	{
	  int i=0;
	  res = mysql_use_result(conn);
	  while( (row = mysql_fetch_row(res)) != NULL )
	  {
	    strcpy(out_par_name_id[i].out_id_1,row[0]);
	    i++;
	  }
	}
    mysql_free_result(res);
  }
//    else
//      printf("\n Serial_number is NUll (get_device_param_name_db_id)");
}

/**
  Function Name     : check_history 
  Input Paramaters  : serial number
  Output Paramaters : NONE
  Result            : SUCCESS/FAILURE
  Description       : check history table
*/
int check_history( char* serial,int limit )
{
  int myreturn = 0;
  
  int id_num = 0;
  char sel_history_id_query[256];
  memset(sel_history_id_query,0,256);
  sprintf( sel_history_id_query,sel_history_id,serial,limit );
  
  if( mysql_query( conn,sel_history_id_query ) )
  {
        logtr("MySQL: ","Please check Your SQL configuration", INFO, conferr); 
        logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
    //printf("\nPlease chack sql server configuration(check_history)");
    //fprintf(stderr,"\n%s\n", mysql_error(conn));
     noSqlError = 1; 
  }
  else
  {
    res = mysql_use_result(conn);
    while( (row = mysql_fetch_row(res)) != NULL )
      id_num++;
    mysql_free_result(res);
    myreturn = id_num;
  }

  
   return ( myreturn > 0 ) ? SUCCESS : FAILURE;
}

/**
  Function Name     : set_unknow_model_name 
  Input Paramaters  : serial number,model name
  Output Paramaters : NONE
  Result            : INSERT OR UPDATE DB
  Description       : insert cpe data unknown table
*/
void set_unknow_model_name( char* serial,char* model )
{

  if( serial && model )
  {
      memset( new_query,0,sizeof( new_query ));
      if( sqlCon() < 0 )
      {
	logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
	//printf("Not connection");
	 noSqlError = 1; 
      }
      else
      {
	    if( check_unknown( serial ) != SUCCESS )
	    {
		sprintf(new_query,insert_unknown,serial,model,"not_in_glossary");
		if (mysql_query(conn,new_query)) 
		{
		  logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
		  logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr); 
		  //printf("\nPlease chack sql server configuration(set_unknow_model_name)");
		  //fprintf(stderr,"\n%s\n", mysql_error(conn));
		   noSqlError = 1; 
		}
		else
		{
		  char unknown_info[128];
		  sprintf(unknown_info,"Inserted unknown table with this serialnumber: %s and modelname: %s ",serial,model);
		  logtr("MySQL: ",unknown_info, INFO, conferr); 
		  memset(unknown_info,0,128);
		  //printf("\n Inserted unknown table\n");
		}
	    }
	}
      
      mysql_close(conn);
  }
//    else
//      printf("\n Serial or Model is NULL (set_unknow_model_name)");
}

/**
  Function Name     : check_unknown
  Input Paramaters  : serial number,model name
  Output Paramaters : NONE
  Result            : SUCCESS/FAILURE
  Description       : check unknown cpe data in db
*/
int check_unknown( char* serial )
{
  int unknown_count = 0;
 if( serial )
 {
    memset(new_query1,0,sizeof(new_query1));
    sprintf(new_query1,sel_unknown,serial);
    if( mysql_query( conn,new_query1 )) 
    {
       logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
       logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr); 
       //printf("\nPlease chack sql server configuration(check_unknown)");
       //fprintf(stderr,"\n%s\n", mysql_error(conn));
        noSqlError = 1; 
    }
    else
    {
      res = mysql_use_result(conn);
      while((row = mysql_fetch_row(res)) != NULL )
      unknown_count++;
    }
    mysql_free_result( res );
 }
//   else
//    printf("\n Serial Number is NULL");
 
 return ( unknown_count > 0 ) ? SUCCESS : FAILURE;
}

/**
  Function Name     : cpe_reboot
  Input Paramaters  : serial number
  Output Paramaters : NONE
  Result            : myreturn
  Description       : read reboot table
 */
int cpe_reboot( char* serial )
{
  int count = 0;
  int myreturn = 0;
  memset(sel_reboot_query,0,sizeof(sel_reboot_query));
   if( sqlCon() < 0 ){
      logtr("error establishing a MySQL connection",(char*)mysql_error(conn), ERROR, conferr);
      //printf("Not connection");
       noSqlError = 1; 
   }
  else
  {
    sprintf( sel_reboot_query,sel_reboot,serial );
    if(mysql_query( conn,sel_reboot_query )) 
    {
       logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
       logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr); 
       //printf("\nPlease chack sql server configuration(cpe_reboot)");
       //fprintf(stderr,"\n%s\n", mysql_error(conn));
      noSqlError = 1; 
    }
    else
    {
	res = mysql_use_result(conn);
	while((row = mysql_fetch_row(res)) != NULL )
	  count++;
	if( count > 0 )
	  myreturn = 1;
	mysql_free_result( res );
    }
  }
  
  mysql_close(conn);
  
  return myreturn;
}

/**
  Function Name     : set_cpe_reboot_db 
  Input Paramaters  : serial number
  Output Paramaters : NONE
  Result            : INSERT OR UPDATE DB
  Description       : set reboot table
*/
void set_cpe_reboot_db( char* serial )
{
  memset(update_reboot_query,0,sizeof( update_reboot_query ));
  if( sqlCon() < 0 ){
     logtr("error establishing a MySQL connection",(char*)mysql_error(conn), ERROR, conferr);
     //printf("Not connection");
      noSqlError = 1; 
  }
  else
  {
    sprintf( update_reboot_query,update_reboot,serial );
    if( mysql_query( conn,update_reboot_query ))
    {
          logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
          logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr); 
       //printf("\nPlease chack sql server configuration(set_cpe_reboot_db)");
       //fprintf(stderr,"\n%s\n",mysql_error(conn));
        noSqlError = 1; 
    }
    else{
      logtr(serial,"CPE with this serial number was rebooted: ", INFO, conferr);
      //printf("\nCPE with [ %s ] serial number was rebooted\n",serial);
    }
  }
  mysql_close(conn);
}

/**
  Function Name     : check_reboot_tbl 
  Input Paramaters  : serial number
  Output Paramaters : reboot_not_exs_count
  Result            : exist or not 
  Description       : check reboot table
*/
int check_reboot_tbl( char* serial )
{
  int reboot_not_exs_count = 1;
  char check_reboot_exists_query[512];
  memset(check_reboot_exists_query,0,sizeof(check_reboot_exists_query));
  sprintf(check_reboot_exists_query,check_reboot_exists,serial);
 
  if( mysql_query( conn,check_reboot_exists_query ))
    {
          logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
          logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr); 
       //printf("\nPlease chack sql server configuration(check_reboot_tbl)");
       //fprintf(stderr,"\n%s\n",mysql_error(conn));
        noSqlError = 1; 
    }
    else
    {
     res = mysql_use_result(conn);
     while(( row = mysql_fetch_row(res) ) != NULL )
     {
      reboot_not_exs_count = 0;
       logtr(serial,"There is this serial in reboot: ", INFO, conferr);
      //printf("\nThere is this serial[ %s ] in reboot ",serial );
     }
     
     mysql_free_result(res);
    }
   
    return reboot_not_exs_count; 
}

char* get_check_and_fw( char* fw_xml,char* serial )
{
  memset(return_fw_xml,0,1024);
  strcpy(return_fw_xml,"NONE");
   if( sqlCon() < 0 ){
     logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
     //printf("Not connection");
      noSqlError = 1; 
   }
  else
  {
   char sel_fw_query[256];
   memset(sel_fw_query,0,256);
   sprintf(sel_fw_query,sel_fw,serial); 
   if( mysql_query( conn,sel_fw_query ))
    {
            logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
            logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
       //printf("\nPlease chack sql server configuration(get_check_and_fw)");
       //fprintf(stderr,"\n%s\n",mysql_error(conn));
        noSqlError = 1; 
    }
   else
   {
    res = mysql_use_result(conn);
    while( ( row = mysql_fetch_row(res) ) != NULL )
      strcpy(return_fw_xml,(char*)get_fw_download_xml(fw_xml,row[0],row[1],row[2]));
    return (char*)return_fw_xml;
   }
   
  }
  mysql_free_result(res);
  mysql_close(conn);
  return return_fw_xml;
}


char* get_fw_download_xml( char* fw_xml,char* file_type,char* url,char* file_size )
{
  memset(fw_finish_xml,0,sizeof(fw_finish_xml));
  sprintf(fw_finish_xml,fw_xml,file_type,url,file_size);
  return (char*)fw_finish_xml;
}

int check_fw_status( char* serial )
{
  int myreturn = FAILURE;
  if( sqlCon() < 0 ){
     logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
     //printf("Not connection in check_fw_status function");
      noSqlError = 1; 
  }
  else
  {
    memset(fw_ifexist_query,0,1024);
    sprintf(fw_ifexist_query,fw_ifexist,serial);
     if( mysql_query( conn,fw_ifexist_query ))
     {
             logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
             logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
       //printf("\nPlease chack sql server configuration(check_fw_status)");
       //fprintf(stderr,"\n%s\n",mysql_error(conn));
        noSqlError = 1; 
     }
    else
    {
	res = mysql_use_result(conn);
	while( ( row = mysql_fetch_row(res) ) != NULL )
	  myreturn = SUCCESS;
	  mysql_free_result(res);
    }
  }

   mysql_close(conn);
   return myreturn;
}

void done_fw_upgrade(int status, char* serial )
{
   if( sqlCon() < 0 ){
     logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
     //printf("Not connection");
      noSqlError = 1; 
   }
  else
  {
    memset(done_upgrade_query,0,sizeof(done_upgrade_query));
    sprintf(done_upgrade_query,done_upgrade,status,serial);
     if( mysql_query( conn,done_upgrade_query ))
     {
        logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
        logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
        //printf("\nPlease chack sql server configuration(done_fw_upgrade)");
       //fprintf(stderr,"\n%s\n",mysql_error(conn));
        noSqlError = 1; 
     }
     else{
        logtr(serial,"MySQL: Upgrade status updated with this serial:", INFO, conferr);
        //printf("\n Upgrade status updated \n");
     }
  }
   mysql_close(conn);
}

void parameterFilter( getParamNames getParNam,getParamValues getParVal,getNewParamValues getNewParVal,char* model)
{
  if( getParNam && getParVal && getNewParVal && model  )
  {
    int i;
    int id_count = get_param_name_db_id( model );

  for(i = 0;i < id_count ;i++)
    memset(getNewParVal[i].getNewValue,0,256);
 
   if( !conn ){
      logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
      //printf("Not connection");
       noSqlError = 1; 
   }
  else
  {
    sprintf(select_param_name_query,select_param_name,model);

    if( mysql_query( conn,select_param_name_query ))
     {
          logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
          logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
        //printf("\nPlease chack sql server configuration(parameterFilter)");
        //fprintf(stderr,"\n%s\n",mysql_error(conn));
	 noSqlError = 1; 
     }
     else
     {
       int j = 0;
       res = mysql_use_result(conn);
       while(( row = mysql_fetch_row(res) ) != NULL )
       {
 	 for(i = 0;i< number_of_names;i++)
 	  if( strcmp(row[0],getParNam[i].getNameStr) == 0 )
 	    strcpy( getNewParVal[j].getNewValue,getParVal[i].getValueStr );
	  j++;
       }
       mysql_free_result(res);
     }
  }
  }
//    else
//      printf("\ngetParNam or getParVal or getNewParVal or model is NULL");
}

char* get_model_id( char* model )
{
  if( !conn ){
     logtr("MySQL: ","error establishing a database connection", ERROR, conferr);
     //printf("Not connection");
     noSqlError = 1; 
  }
  else
  {
      char select_model_id_query[256];
      memset(returnModelId,0,5);
      memset(select_model_id_query,0,256);
      sprintf(select_model_id_query,select_model_id,model);
      
      if( mysql_query( conn,select_model_id_query ))
      {
	  logtr("MySQL: ","Check Your SQL configuration", INFO, conferr); 
          logtr("MySQL: ",(char*)mysql_error(conn), ERROR, conferr);
 	//printf("\nPlease chack sql server configuration(get_model_id)");
 	//fprintf(stderr,"\n%s\n",mysql_error(conn));
	 noSqlError = 1; 
      }
     else
     {
       res = mysql_use_result(conn);
       while(( row = mysql_fetch_row(res) ) != NULL )
	 strcpy(returnModelId,row[0]);
       mysql_free_result(res);
     }
  }
  
  return (char*)returnModelId;
  
}


